package com.ikas.ai.utils;

import com.alibaba.fastjson.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 数据库jdbc工具类
 *
 * @author dbh
 */
@SuppressWarnings("all")
public class JdbcUtils {

    protected static Logger log = LoggerFactory.getLogger(JdbcUtils.class);


    public static final String STR = "jdbc:mysql://%s/%s?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai";

    public static final String SQL_COLUMN_FIRST = "SELECT * FROM %s.%s limit 1";

    public static final String SQL_COUNT = "select count(1) as total from %s";

    public static final String SQL_QUERY = "select * from  %s  limit %s,%s";

    /**
     * 获取一个连接
     *
     * @return
     * @throws Exception
     */
    public static Connection getConnection() throws Exception {
        try {
            // 1.获取配置文件信息
            InputStream resource = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(resource);
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");
            // 2. 加载驱动
            Class.forName(driverClass);
            Connection connection = DriverManager.getConnection(url, user, password);
            return connection;
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 获取一个连接
     *
     * @return
     * @throws Exception
     */
    public static Connection getConnection(JSONObject jsonObject) {
        try {
            //驱动信息
            String driverClass = jsonObject.getString("driverClass");
            String host = jsonObject.getString("host");
            int port = jsonObject.getIntValue("port");
            //数据库实例
            String dbname = jsonObject.getString("dbName");
            String url = String.format(STR, host + ":" + port, dbname);
            log.info("url={}", url);
            String user = jsonObject.getString("userName");
            String password = jsonObject.getString("password");
            // 2. 加载驱动
            Class.forName(driverClass);
            Connection connection = DriverManager.getConnection(url, user, password);
            return connection;
        } catch (Exception e) {
            log.error("初始化数据库连接异常," + e.getMessage());
            return null;
        }
    }

    /**
     * @return ArrayList
     * @use 获取某张表的列名
     */
    public static ArrayList<String> getColumnNames(JSONObject jsonObject) {
        ArrayList<String> columnNames = null;
        Connection con = null;
        Statement stmt = null;
        try {
            con = getConnection(jsonObject);
            stmt = con.createStatement();
            String sql = String.format(SQL_COLUMN_FIRST, jsonObject.getString("dbName"), jsonObject.get("dbTable"));
            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            int count = rsmd.getColumnCount();
            columnNames = new ArrayList<String>(count);
            for (int i = 0; i < count; i++) {
                columnNames.add(rsmd.getColumnName(i + 1));
            }
        } catch (SQLException e) {
            log.error("数据库连接失败", e);
        }
        return columnNames;
    }


    public static <T> List<T> query(Connection connection, String sql, Class<T> clazz, Object... args) {
        log.info("execute sql is : {}", sql);
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<T> list = new ArrayList();
        try {
            // 2.预编译一个sql语句，返回一个PrepareStatement对象
            preparedStatement = connection.prepareStatement(sql);
            // 3.填充占位符
            for (int i = 1; i <= args.length; i++) {
                preparedStatement.setObject(i, args[i - 1]);
            }
            // 4。执行sql,得到结果集
            resultSet = preparedStatement.executeQuery();
            // 5.获取元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            // 6.获取一个类的反射实例
            T t = clazz.newInstance();
            while (resultSet.next()) {
                // 7.遍历得到每一行数据
                if (t instanceof JSONObject) {
                    JSONObject jsonObject = new JSONObject(true);
                    for (int i = 1; i <= columnCount; i++) {
                        //获取列的值
                        Object object = resultSet.getObject(i);
                        //获取列的字段名
                        String columnLabel = metaData.getColumnLabel(i);
                        jsonObject.put(columnLabel, object);
                    }
                    list.add((T) jsonObject);
                } else {
                    for (int i = 1; i <= columnCount; i++) {
                        // 7.1获取列值
                        Object object = resultSet.getObject(i);
                        // 7.2获取列别名
                        String columnLabel = metaData.getColumnLabel(i);
                        // 7.3获取属性并设置属性的值
                        Field field = clazz.getDeclaredField(columnLabel);
                        field.setAccessible(true);
                        field.set(t, object);
                    }
                    list.add(t);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("查询数据失败," + e.getMessage());
        } finally {
            closeConnection(connection, preparedStatement, resultSet);
        }
        return list;
    }
    public static <T> List<T> queryNoCloseConnection(Connection connection, String sql, Class<T> clazz, Object... args) {
        log.info("execute sql is : {}", sql);
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<T> list = new ArrayList();
        try {
            // 2.预编译一个sql语句，返回一个PrepareStatement对象
            preparedStatement = connection.prepareStatement(sql);
            // 3.填充占位符
            for (int i = 1; i <= args.length; i++) {
                preparedStatement.setObject(i, args[i - 1]);
            }
            // 4。执行sql,得到结果集
            resultSet = preparedStatement.executeQuery();
            // 5.获取元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            // 6.获取一个类的反射实例
            T t = clazz.newInstance();
            while (resultSet.next()) {
                // 7.遍历得到每一行数据
                if (t instanceof JSONObject) {
                    JSONObject jsonObject = new JSONObject(true);
                    for (int i = 1; i <= columnCount; i++) {
                        //获取列的值
                        Object object = resultSet.getObject(i);
                        //获取列的字段名
                        String columnLabel = metaData.getColumnLabel(i);
                        jsonObject.put(columnLabel, object);
                    }
                    list.add((T) jsonObject);
                } else {
                    for (int i = 1; i <= columnCount; i++) {
                        // 7.1获取列值
                        Object object = resultSet.getObject(i);
                        // 7.2获取列别名
                        String columnLabel = metaData.getColumnLabel(i);
                        // 7.3获取属性并设置属性的值
                        Field field = clazz.getDeclaredField(columnLabel);
                        field.setAccessible(true);
                        field.set(t, object);
                    }
                    list.add(t);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("查询数据失败," + e.getMessage());
        } finally {
            closeConnection(null, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 查询总数
     *
     * @param connection
     * @param sql
     * @param obj
     * @return
     */
    public static Long count(Connection connection, String sql, Object... obj) {
        PreparedStatement preparedStatement = null;
        try {
            // 2.预编译sql，返回一个PrepareStatement实例
            preparedStatement = connection.prepareStatement(sql);
            // 3.填充占位符
            for (int i = 1; i <= obj.length; i++) {
                preparedStatement.setObject(i, obj[i - 1]);
            }
            // 4.执行
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getLong(1);
            }
            return 0L;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.关闭资源
            closeConnection(null, preparedStatement);
        }
        return 0L;
    }


    public static int update(Connection connection, String sql, Object... obj) {
        PreparedStatement preparedStatement = null;
        try {
            // 2.预编译sql，返回一个PrepareStatement实例
            preparedStatement = connection.prepareStatement(sql);
            // 3.填充占位符
            for (int i = 0; i < obj.length; i++) {
                preparedStatement.setObject(i + 1, obj[i]);
            }
            // 4.执行
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 5.关闭资源
            closeConnection(null, preparedStatement);
        }
        return 0;
    }


    /**
     * 关闭连接
     *
     * @param connection
     * @param statement
     */
    public static void closeConnection(Connection connection, Statement statement) {
        try {
            if (connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 关闭连接
     *
     * @param connection
     * @param statement
     */
    public static void closeConnection(Connection connection, Statement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {

                resultSet.close();
            }
            if (connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}
